/* MSCI.do (STATA)
	Construct MSCI data.
	by Ralph Koijen & Motohiro Yogo */

#delimit ;
clear all;
set more off;
set type double;

cap log close;
log using MSCI, replace;

/* Define local and global variables */

local directory = "../../Data_nonpublic/MSCI";

do global;


/* Step 1: Construct market value */

/* Load data */

import excel using "`directory'/MSCI MV PBV",
	sheet("Market Value") cellra(B11) first clear;

/* Rename variables */

foreach var of varlist Argentina-UnitedStates {;
	rename `var' market`var';
};

/* Reshape in long format */

reshape long market, i(DATE) j(Name) string;

/* Convert to US$ billion */

replace market = market/1e3;

/* Save data */

tempfile market;
save `market';


/* Step 2: Construct market-to-book equity */

/* Load data */

import excel using "`directory'/MSCI MV PBV",
	sheet("Price to BV") cellra(B11) first clear;

/* Rename variables */

foreach var of varlist Argentina-UnitedStates {;
	rename `var' pbv`var';
};

/* Reshape in long format */

reshape long pbv, i(DATE) j(Name) string;


/* Step 3: Merge market value and market-to-book equity */

merge 1:1 DATE Name using `market',
	nogen keep(master match);

/* Fix variables */

replace Name = regexr(Name,regexs(0),regexs(1)+" "+regexs(2))
	if regexm(Name,"([a-z])([A-Z])");

/* Merge country code */

merge m:1 Name using Countries,
	keepusing(country)
	nogen keep(match);

drop Name;

/* Construct variables */

gen int year = year(DATE);

/* Keep year-end data */

sort year country DATE;
by year country: keep if _n==_N;

drop DATE;

/* Sample criteria */

keep if inrange(year,$year_min,$year_max);

/* Label variables */

order year country;

label var year		"Year";

label var pbv		"Market-to-book equity";
label var market	"Market value (US$ billion)";

/* Save data */

sort year country;

save MSCI, replace;

log close;
